package ces.frame.module.homePage.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.orm.hibernate3.HibernateTemplate;

import ces.architecture.util.SqlUtil;
import ces.frame.common.CreateUuid;
import ces.frame.util.CommonUtil;
import ces.frame.util.anyPlace.APCommon;
import ces.frame.util.coral.tag.table.DataList;
import ces.frame.util.coral.tag.table.Page;
import ces.frame.util.internal.ICommon;

public class HomePageDAOImpl implements HomePageDAO{

	private DataList dataList = null;
	private HibernateTemplate hibernateTemplate = null;
	private JdbcTemplate jdbcTemplate = null;
	
	public DataList getDataList() {
		return dataList;
	}

	public void setDataList(DataList dataList) {
		this.dataList = dataList;
	}

	public HibernateTemplate getHibernateTemplate() {
		return hibernateTemplate;
	}

	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getNotice(String state,String menuId,String rownum) throws Exception {
		StringBuffer sql = new StringBuffer();
		if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
			sql.append("select * from (select t.id,a.id fileId,substr(t.create_date,0,10)create_date,");
			sql.append("t.title_proper,t.content_path,a.file_name,t.summary from t_web_file t left join  T_WEB_UPLOADED a on t.id=a.owner_id");
			sql.append(" where t.issue_status='").append(state).append("' and t.menu_id='");
			sql.append(menuId).append("'");
			sql.append(" order by t.create_date desc) t");
			sql.append(" where rownum < ").append(rownum);
		}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
			sql.append("select top " + rownum + " * from (select t.id,a.id fileId,substring(t.create_date,0,10)create_date,");
			sql.append("t.title_proper,t.content_path,a.file_name,t.summary from t_web_file t left join  T_WEB_UPLOADED a on t.id=a.owner_id");
			sql.append(" where t.issue_status='").append(state).append("' and t.menu_id='");
			sql.append(menuId).append("') t");
			sql.append(" order by t.create_date desc");
		}
		return jdbcTemplate.queryForList(sql.toString().toUpperCase());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getImagesCenter(String code, String rownum)throws Exception {
		StringBuffer sql = new StringBuffer();
		String base_tableName = ICommon.getBaseTableName(code);
		String atth_tableName = ICommon.getAtthTableName(code);
		/*sql.append("select * from (select a.unique_code,t.unique_code pid,t.ztm from ").append(base_tableName).append(" t,T_ARCHIVE_RECOMMEND r,");
		sql.append(atth_tableName).append(" a where t.unique_code=r.record_id and t.unique_code=a.archive_unique_code");
		sql.append(" and r.ARCHIVETYPE_CODE='").append(code).append("' and t.DELETE_FLAG='0' and a.file_type='2' order by r.recomm_date desc");
		sql.append(" ) where rownum <=").append(rownum);*/
		
		if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
			sql.append("select * from (select a.unique_code,t.unique_code pid,t.ztm from ").append(base_tableName).append(" t,");
			sql.append(atth_tableName).append(" a where t.unique_code=a.archive_unique_code and t.open_flag ='全文开放'");
			sql.append(" and t.DELETE_FLAG='0' and a.file_type='1' and upper(atth_type) like '%JPG%' and t.temp_formal='formal' order by t.unique_code desc");
			sql.append(" ) where rownum <=").append(rownum);
		}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
			sql.append("select top " + rownum + " * from (select a.unique_code,t.unique_code pid,t.ztm from ").append(base_tableName).append(" t,");
			sql.append(atth_tableName).append(" a where t.unique_code=a.archive_unique_code and t.open_flag ='全文开放'");
			sql.append(" and t.DELETE_FLAG='0' and a.file_type='1' and upper(atth_type) like '%JPG%' and t.temp_formal='formal') t order by t.unique_code desc");
		}
		System.out.println("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getFile(String id) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select t.file_name,t.file_path from T_WEB_UPLOADED t where t.id='");
		sql.append(id).append("'");
		return jdbcTemplate.queryForList(sql.toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getAtthFile(String code, String id,String fileType)throws Exception {
		StringBuffer sql = new StringBuffer();
		String base_tableName = ICommon.getBaseTableName(code);
		String atth_tableName = ICommon.getAtthTableName(code);
		sql.append("select t.atth_name,t.atth_path,t.atth_type from ");
		sql.append(atth_tableName).append(" t,").append(base_tableName).append(" a");
		sql.append(" where a.unique_code=t.archive_unique_code and a.unique_code='").append(id);
		sql.append("' and t.file_type='").append(fileType).append("'");
		return jdbcTemplate.queryForList(sql.toString());
	}

	@Override
	public Page seeAllData(Page page, String status, String menuId)throws Exception {
		StringBuffer sql = new StringBuffer();
		if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
			sql.append("select t.id,substr(t.create_date,0,10)create_date,");
			sql.append("t.title_proper,t.content_path,t.summary from t_web_file t");
			sql.append(" where t.issue_status='").append(status).append("' and t.menu_id='");
			sql.append(menuId).append("'");
			sql.append(" order by t.create_date desc");
			/*sql.append("select t.id,a.id fileId,substr(t.create_date,0,10)create_date,");
			sql.append("t.title_proper,t.content_path,a.file_name,t.summary from t_web_file t left join  T_WEB_UPLOADED a on t.id=a.owner_id");
			sql.append(" where t.issue_status='").append(status).append("' and t.menu_id='");
			sql.append(menuId).append("'");
			sql.append(" order by t.create_date desc");*/
		}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
			sql.append("select t.id,a.id fileId,substring(t.create_date,0,10)create_date,");
			sql.append("t.title_proper,t.content_path,a.file_name,t.summary from t_web_file t left join  T_WEB_UPLOADED a on t.id=a.owner_id");
			sql.append(" where t.issue_status='").append(status).append("' and t.menu_id='");
			sql.append(menuId).append("'");
			sql.append(" order by t.create_date desc");
		}
		return this.dataList.generateJdbcPage(sql.toString(), page);
	}
	
	public Page seeQzData(Page page)throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select a.id,a.fonds_code, a.fonds_name from T_ARCHIVE_DAILY_FONDS a order by a.id");
		return this.dataList.generateJdbcPage(sql.toString(), page);
	}

	@Override
	public Page seeAllImgData(Page page, String code) throws Exception {
		StringBuffer sql = new StringBuffer();
		String base_tableName = ICommon.getBaseTableName(code);
		String atth_tableName = ICommon.getAtthTableName(code);
/*		sql.append("select a.unique_code,t.unique_code pid,t.ztm,a.atth_path,a.real_name from ").append(base_tableName).append(" t,T_ARCHIVE_RECOMMEND r,");
		sql.append(atth_tableName).append(" a where t.unique_code=r.record_id and t.unique_code=a.archive_unique_code");
		sql.append(" and r.ARCHIVETYPE_CODE='").append(code).append("' and t.DELETE_FLAG='0' and a.file_type='2' order by r.recomm_date desc");
*/		
		sql.append("select a.unique_code,t.unique_code pid,t.ztm,a.atth_path,a.real_name from ").append(base_tableName).append(" t,");
		sql.append(atth_tableName).append(" a where t.unique_code=a.archive_unique_code and t.open_flag ='全文开放'");
		sql.append(" and t.DELETE_FLAG='0' and a.file_type='1' and t.temp_formal='formal' order by a.unique_code desc");
		return this.dataList.generateJdbcPage(sql.toString(), page);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> seeDetailed(String id) throws Exception {
		StringBuffer sql = new StringBuffer();
		if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
			sql.append("select t.id,a.id fileId,substr(t.create_date,0,10)create_date,");
			sql.append("t.title_proper,t.content_path,a.file_name,t.summary from t_web_file t left join  T_WEB_UPLOADED a on t.id=a.owner_id");
			sql.append(" where t.id='").append(id).append("'");
		}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
			sql.append("select t.id,a.id fileId,substring(t.create_date,0,10)create_date,");
			sql.append("t.title_proper,t.content_path,a.file_name,t.summary from t_web_file t left join  T_WEB_UPLOADED a on t.id=a.owner_id");
			sql.append(" where t.id='").append(id).append("'");	
		}
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> seeQzDetailed(String id) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_ARCHIVE_DAILY_FONDS t");
		sql.append(" where t.id='").append(id).append("'");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> seeQzAtthDetailed(String fondId, String atthId) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_FONDS_UPLOADED t");
		sql.append(" where 1=1");
		if(!"".equals(fondId)){
			sql.append(" and t.FONDS_ID='" + fondId + "'");
		}
		if(!"".equals(atthId)){
			sql.append(" and t.ID='" + atthId + "'");
		}
		return jdbcTemplate.queryForList(sql.toString());
	}

	@Override
	public Page getWorkTasks(Page page,String userId, String status, String where) throws Exception {
		StringBuffer sql = new StringBuffer();
		
		//add at 20160718 添加专题编研流程信息返回
		if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
			sql.append("select w.id as sheetId,  wf.status,u.user_name create_user ,w.create_date,w.sheet_name,");
			sql.append(" w.sheet_type,f.name, wf.sheet_id,wf.review_user as review_user,wf.FLOW_NAME,n.flow_id,wf.id as flowId,");
			sql.append(" wf.flow_node,w.status as sheetStatus, (select distinct g.batch_id from T_CONFIG_WORKSHEET_BATCH_RELA g");
			sql.append(" where wf.sheet_id=g.sheet_id and rownum=1) batch_id,  (  select b.organize_name from " + CommonUtil.SYSUSER + ".t_org_user a left join " + CommonUtil.SYSUSER + ".t_org b on  a.organize_id = b.organize_id where a.user_id =w.create_user ) createUserId  from t_config_worksheet w,");
			sql.append(" t_config_worksheet_flow wf,t_config_flow_node n, t_config_flow f," + CommonUtil.SYSUSER + ".t_user u");
			sql.append(" where wf.sheet_id = w.id and w.create_user = u.user_id and wf.status in(").append(status).append(") and n.order_no=wf.flow_node");
			//sql.append(" and w.sheet_type = f.code  and f.id = n.flow_id  and wf.review_user = '").append(userId).append("' order by w.id desc");
			sql.append(" and w.sheet_type = f.code  and f.id = n.flow_id  and wf.review_user = '").append(userId).append("'");
			//sql.append(",w.id as createId ");
			sql.append(" union all ");
			sql.append(" select t.id sheetId, to_char(t.status), m.register_user_name, to_char(m.create_time, 'YYYY-MM-DD') create_date, m.zt_name, '' lx, '专题编研复审' mc, " +
					"null,null,t.activity_id,null,null,null,'ztby',m.id,null");
			sql.append(" from T_WF_WORKITEM t, T_MH_ZTBY m");
			sql.append(" where t.process_instance_id  =m.process_instance_id " + where);
		}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
			sql.append("select w.id as sheetId,  wf.status,u.user_name create_user ,w.create_date,w.sheet_name,");
			sql.append(" w.sheet_type,f.name, wf.sheet_id,wf.review_user as review_user,wf.FLOW_NAME,n.flow_id,wf.id as flowId,");
			sql.append(" wf.flow_node,w.status as sheetStatus, (select top 1 g.batch_id from T_CONFIG_WORKSHEET_BATCH_RELA g");
			sql.append(" where wf.sheet_id=g.sheet_id) batch_id,(  select b.organize_name from " + CommonUtil.SYSUSER + ".dbo.t_org_user a left join " + CommonUtil.SYSUSER + ".dbo.t_org b on  a.organize_id = b.organize_id where a.user_id =w.create_user ) createUserId from t_config_worksheet w,");
			sql.append(" t_config_worksheet_flow wf,t_config_flow_node n, t_config_flow f," + CommonUtil.SYSUSER + ".dbo.t_user u");
			sql.append(" where wf.sheet_id = w.id and w.create_user = u.user_id and wf.status in(").append(status).append(") and n.order_no=wf.flow_node");
			//sql.append(" and w.sheet_type = f.code  and f.id = n.flow_id  and wf.review_user = '").append(userId).append("' order by w.id desc");
			sql.append(" and w.sheet_type = f.code  and f.id = n.flow_id  and wf.review_user = '").append(userId).append("'");
		}
		System.out.println(sql+"===sql");
		return this.dataList.generateJdbcPage("select t.* from (" + sql.toString() + ") t order by t.create_date desc, t.sheetId desc", page);
	}

	@Override
	public int getWorkTaskCount(String userId, String status)throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) ");
		sql.append(" from t_config_worksheet w,");
		sql.append(" t_config_worksheet_flow wf,t_config_flow_node n, t_config_flow f," + CommonUtil.SYSUSER + ".t_user u");
		sql.append(" where wf.sheet_id = w.id and w.create_user = u.user_id and wf.status in(").append(status).append(") and n.order_no=wf.flow_node");
		sql.append(" and w.sheet_type = f.code  and f.id = n.flow_id  and wf.review_user = '").append(userId).append("' order by w.id desc");
		return jdbcTemplate.queryForInt(sql.toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getXmysCharts(String code, String year) {
		StringBuffer sql = new StringBuffer();
		String base_table_Name = ICommon.getBaseTableName(code);
		int maxY = Integer.valueOf(year);
		int minY = maxY - 10;
		sql.append(" select a.y,nvl(b.s,0)s from (").append(APCommon.getYearSql(maxY)).append(") a left join (");
		sql.append(" select substr(t.accept_date,1,4) y,count(1)s from ").append(base_table_Name).append(" t where t.accept_date >'0' and t.delete_flag='0'");
		sql.append(" and substr(t.accept_date,1,4)<='").append(maxY).append("' and substr(t.accept_date,1,4)>'").append(minY).append("'");
		sql.append(" group by substr(t.accept_date,1,4))b on a.y=b.y order by a.y");
		return this.jdbcTemplate.queryForList(sql.toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDaqkCharts(String year,int afterYear) {
		StringBuffer sql = new StringBuffer();
		int y = Integer.valueOf(year);
		sql.append("select * from (");
		sql.append(" select t.nd,nvl(count(1),0)s,'文书档案'x from t_ar_00W1_base t where t.delete_flag='0'");
		sql.append(" and t.temp_formal='formal' and t.nd > '0' group by t.nd union");
		
		sql.append(" select substr(t.receive_date,1,4) nd,nvl(count(1),0)s,'规划档案'x from t_ar_GGHA_base t where t.delete_flag='0'");
		sql.append(" and t.temp_formal='formal' and t.receive_date > '0'  group by substr(t.receive_date,1,4)  union ");
		
		sql.append(" select substr(t.receive_date,1,4) nd,nvl(count(1),0)s,'建筑档案'x from t_ar_GJZA_base t where t.delete_flag='0'");
		sql.append(" and t.temp_formal='formal' and t.receive_date > '0'  group by substr(t.receive_date,1,4) union");
		
		sql.append(" select substr(t.receive_date,1,4) nd,nvl(count(1),0)s,'竣工档案'x from t_ar_GJGA_base t where t.delete_flag='0'");
		sql.append(" and t.temp_formal='formal' and t.receive_date > '0'  group by substr(t.receive_date,1,4) union");
		
		sql.append(" select t.year,nvl(count(1),0)s,'视频档案'x from t_ar_1T12_base t where t.delete_flag='0'");
		sql.append(" and t.temp_formal='formal' and t.year > '0' group by t.year union");
		
		sql.append(" select t.nd,nvl(count(1),0)s,'照片档案'x from t_ar_0t24_base t where t.delete_flag='0'");
		sql.append(" and t.temp_formal='formal' and t.nd > '0' group by t.nd");
		
		sql.append(") a where to_number(a.nd)<="+(y)+" and to_number(a.nd)>"+(y-afterYear));
		
		return this.jdbcTemplate.queryForList(sql.toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getLysCharts(String year, int afterYear) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( select substr(t.begin_date,1,7)rq,nvl(count(1),0)s,'竣工档案'type from T_ARCHIVE_RECEP_USE t");
		sql.append(" join T_ARCHIVE_RECEP_USE_DETAIL a on t.exborrow_no = a.exborrow_id");
		sql.append(" where a.archive_type_id in('GJGA','GJGW') and ");
		sql.append(" trunc(to_date(t.begin_date,'yyyy-mm-dd HH24:mi:ss'),'mm')>=trunc(add_months (to_date('");
		sql.append(year).append("','yyyy-mm'),-").append(afterYear+1).append("),'mm')");
		sql.append(" and trunc(to_date(t.begin_date,'yyyy-mm-dd HH24:mi:ss'),'mm')<trunc(to_date('").append(year).append("','yyyy-mm'),'mm')");
		sql.append(" and t.exborrow_state='4' group by substr(t.begin_date,1,7)  order by substr(t.begin_date,1,7))");
		sql.append(" union all");
		
		sql.append(" select * from ( select substr(t.begin_date,1,7)rq,nvl(count(1),0)s,'建筑档案' from T_ARCHIVE_RECEP_USE t");
		sql.append(" join T_ARCHIVE_RECEP_USE_DETAIL a on t.exborrow_no = a.exborrow_id");
		sql.append(" where a.archive_type_id in('GJZA','GJZW') and ");
		sql.append(" trunc(to_date(t.begin_date,'yyyy-mm-dd HH24:mi:ss'),'mm')>=trunc(add_months (to_date('");
		sql.append(year).append("','yyyy-mm'),-").append(afterYear+1).append("),'mm')");
		sql.append(" and trunc(to_date(t.begin_date,'yyyy-mm-dd HH24:mi:ss'),'mm')<trunc(to_date('").append(year).append("','yyyy-mm'),'mm')");
		sql.append(" and t.exborrow_state='4' group by substr(t.begin_date,1,7)  order by substr(t.begin_date,1,7))");
		sql.append(" union all");
		
		sql.append(" select * from ( select substr(t.begin_date,1,7)rq,nvl(count(1),0)s,'规划档案' from T_ARCHIVE_RECEP_USE t");
		sql.append(" join T_ARCHIVE_RECEP_USE_DETAIL a on t.exborrow_no = a.exborrow_id");
		sql.append(" where a.archive_type_id in('GGHA','GGHW') and ");
		sql.append(" trunc(to_date(t.begin_date,'yyyy-mm-dd HH24:mi:ss'),'mm')>=trunc(add_months (to_date('");
		sql.append(year).append("','yyyy-mm'),-").append(afterYear+1).append("),'mm')");
		sql.append(" and trunc(to_date(t.begin_date,'yyyy-mm-dd HH24:mi:ss'),'mm')<trunc(to_date('").append(year).append("','yyyy-mm'),'mm')");
		sql.append(" and t.exborrow_state='4' group by substr(t.begin_date,1,7)  order by substr(t.begin_date,1,7))");
		return this.jdbcTemplate.queryForList(sql.toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> getArchivesDetailed(String code, String id)
			throws Exception {
		StringBuffer sql = new StringBuffer();
		String tableName = ICommon.getBaseTableName(code);
		sql.append("SELECT T.UNIQUE_CODE,T.ZTM,'' ARCHIVES_TYPE,T.BGQX,T.ZRZ,T.JH,T.ND,");
		//sql.append(" T.PHOTO_DATE,T.PHOTO_GRAPHER,T.REMARK FROM ").append(tableName).append(" T");
		sql.append(" '' PHOTO_DATE,'' PHOTO_GRAPHER,'' REMARK FROM ").append(tableName).append(" T");
		sql.append(" WHERE T.UNIQUE_CODE='").append(id).append("'");
		List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql.toString());
		if(list != null && !list.isEmpty()){
			return list.get(0);
		}
		return null;
	}

	@Override
	public int setListShowRow(String row, String userId, String userName)
			throws Exception {
		String sql = " delete t_config_listrow t where t.user_id=?";
		this.jdbcTemplate.update(sql, new String[]{userId});
		sql = " insert into t_config_listrow(id,user_id,user_name,row_number)values(?,?,?,?)";
		return this.jdbcTemplate.update(sql, new String[]{CreateUuid.getUuid(),userId,userName,row});
	}

	@Override
	public List<Map<String, Object>> getExborrowData() throws Exception {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
			sql.append(" select MONTH,SUM from ");
			sql.append(" (select trunc(to_date(a.exborrow_data, 'YYYY-MM-DD'),'MM') MONTH, count(1) SUM");
			sql.append(" from t_archive_recep_exborrow a ");
			sql.append(" group by trunc(to_date(a.exborrow_data, 'YYYY-MM-DD'),'MM')");
			sql.append(" order by trunc(to_date(a.exborrow_data, 'YYYY-MM-DD'),'MM')) t");
		}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
			sql.append(" select MONTH,SUM from ");
			sql.append(" (select SUBSTRING(a.exborrow_data, 1, 7) MONTH, count(1) SUM");
			sql.append(" from t_archive_recep_exborrow a ");
			sql.append(" group by SUBSTRING(a.exborrow_data, 1, 7)) t");
			sql.append(" order by t.MONTH");
		}
		System.out.println("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString());
	}

	//按年统计温湿度
	@Override
	public List<Map<String, Object>> getTemperature(String year) {
		
		String updateSql = "UPDATE T_STORAGE_WSDTJ T SET VALUE = '0' WHERE t.value like '%-%'";
		jdbcTemplate.update(updateSql);
		
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		
		sql.append("select '"+year+"-'||m.mm as DATE_, nvl(a.num, 0) as VALUE_ from (select 1 as mm from dual " +
				"union all select 2 from dual union all select 3 from dual union all select 4 from dual union all " +
				"select 5 from dual union all select 6 from dual union all select 7 from dual union all " +
				"select 8 from dual union all select 9 from dual union all select 10 from dual union all " +
				"select 11 from dual union all select 12 from dual) m left join (select extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) as mm," +
				"trunc(sum(to_number(value))/60,1) as num from T_STORAGE_WSDTJ t where dw = 'C' AND T.SBSJ LIKE '%"+year+"%' GROUP BY " +
				"extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) order" +
				" by extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) DESC) a on m.mm = a.mm order by m.mm");
		
		System.out.println("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString());
	}
	@Override
	public List<Map<String, Object>> getHumidity(String year) {
		String updateSql = "UPDATE T_STORAGE_WSDTJ T SET VALUE = '0' WHERE t.value like '%-%'";
		jdbcTemplate.update(updateSql);
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append("select '"+year+"-'||m.mm as DATE_, nvl(a.num, 0) as VALUE_ from (select 1 as mm from dual " +
				"union all select 2 from dual union all select 3 from dual union all select 4 from dual union all " +
				"select 5 from dual union all select 6 from dual union all select 7 from dual union all " +
				"select 8 from dual union all select 9 from dual union all select 10 from dual union all " +
				"select 11 from dual union all select 12 from dual) m left join (select extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) as mm," +
				"trunc(sum(to_number(value))/60,1) as num from T_STORAGE_WSDTJ t where dw = '%' AND T.SBSJ LIKE '%"+year+"%' GROUP BY " +
				"extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) order" +
				" by extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) DESC) a on m.mm = a.mm order by m.mm");
		System.out.println("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	
	
	
	//按年统计温湿度
		@Override
		public List<Map<String, Object>> getTemperatureBhg(String year) {
			
			String updateSql = "UPDATE T_STORAGE_WSDTJ T SET VALUE = '0' WHERE t.value like '%-%'";
			jdbcTemplate.update(updateSql);
			
			// TODO Auto-generated method stub
			StringBuffer sql = new StringBuffer();
			
			sql.append("select '"+year+"-'||m.mm as DATE_, nvl(a.num, 0) as VALUE_ from (select 1 as mm from dual " +
					"union all select 2 from dual union all select 3 from dual union all select 4 from dual union all " +
					"select 5 from dual union all select 6 from dual union all select 7 from dual union all " +
					"select 8 from dual union all select 9 from dual union all select 10 from dual union all " +
					"select 11 from dual union all select 12 from dual) m left join (select extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) as mm," +
					"trunc(sum(to_number(value))/60,1) as num from T_STORAGE_WSDTJ t where dw = 'C' AND T.SBSJ LIKE '%"+year+"%' GROUP BY " +
					"extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) order" +
					" by extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) DESC) a on m.mm = a.mm and (a.num <'14' or a.num>'24') order by m.mm");
			
			System.out.println("sql="+sql.toString());
			return jdbcTemplate.queryForList(sql.toString());
		}
		@Override
		public List<Map<String, Object>> getHumidityBhg(String year) {
			String updateSql = "UPDATE T_STORAGE_WSDTJ T SET VALUE = '0' WHERE t.value like '%-%'";
			jdbcTemplate.update(updateSql);
			// TODO Auto-generated method stub
			StringBuffer sql = new StringBuffer();
			sql.append("select '"+year+"-'||m.mm as DATE_, nvl(a.num, 0) as VALUE_ from (select 1 as mm from dual " +
					"union all select 2 from dual union all select 3 from dual union all select 4 from dual union all " +
					"select 5 from dual union all select 6 from dual union all select 7 from dual union all " +
					"select 8 from dual union all select 9 from dual union all select 10 from dual union all " +
					"select 11 from dual union all select 12 from dual) m left join (select extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) as mm," +
					"trunc(sum(to_number(value))/60,1) as num from T_STORAGE_WSDTJ t where dw = '%' AND T.SBSJ LIKE '%"+year+"%'   GROUP BY " +
					"extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) order" +
					" by extract(month from trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'MM')) DESC) a on m.mm = a.mm and (a.num <'45' or a.num>'60')  order by m.mm");
			System.out.println("sql="+sql.toString());
			return jdbcTemplate.queryForList(sql.toString());
		}
		
	
	
	
	@Override
	public List<Map<String, Object>> getTemperature2(String month) {
		
		String updateSql = "UPDATE T_STORAGE_WSDTJ T SET VALUE = '0' WHERE t.value like '%-%'";
		jdbcTemplate.update(updateSql);
		
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		   sql.append(" select * from ");
			sql.append(" (select trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') date_,trunc(sum(to_number(t.value))/2,1) value_ ");
			sql.append(" from T_STORAGE_WSDTJ t where t.dw = 'C' AND sbsj LIKE '%"+month+"%'");
			sql.append(" group by trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') ");
			sql.append(" order by trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') )");
		System.out.println("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	
	@Override
	public List<Map<String, Object>> getTemperatureBhg2(String month) {
		
		String updateSql = "UPDATE T_STORAGE_WSDTJ T SET VALUE = '0' WHERE t.value like '%-%'";
		jdbcTemplate.update(updateSql);
		
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from (");
		   sql.append(" select date_,value_ from ");
			sql.append(" (select trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') date_,trunc(sum(to_number(t.value))/2,1) value_ ");
			sql.append(" from T_STORAGE_WSDTJ t where t.dw = 'C' AND sbsj LIKE '%"+month+"%'");
			sql.append(" group by trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') ");
			sql.append(" order by trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') )");
			sql.append(") A WHERE (A.value_<'14' OR A.value_>'24')");
		System.out.println("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	@Override
	public List<Map<String, Object>> getHumidity2(String month) {
		String updateSql = "UPDATE T_STORAGE_WSDTJ T SET VALUE = '0' WHERE t.value like '%-%'";
		jdbcTemplate.update(updateSql);
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		   sql.append(" select * from ");
			sql.append(" (select trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') date_,trunc(sum(to_number(t.value))/2,1) value_ ");
			sql.append(" from T_STORAGE_WSDTJ t where t.dw = '%' AND sbsj LIKE '%"+month+"%'");
			sql.append(" group by trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') ");
			sql.append(" order by trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') )");
		System.out.println("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	@Override
	public List<Map<String, Object>> getHumidityBhg2(String month) {
		String updateSql = "UPDATE T_STORAGE_WSDTJ T SET VALUE = '0' WHERE t.value like '%-%'";
		jdbcTemplate.update(updateSql);
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from (");
		   sql.append(" select date_,value_ from ");
			sql.append(" (select trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') date_,trunc(sum(to_number(t.value))/2,1) value_ ");
			sql.append(" from T_STORAGE_WSDTJ t where t.dw = '%' AND sbsj LIKE '%"+month+"%'");
			sql.append(" group by trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') ");
			sql.append(" order by trunc(to_date(t.sbsj, 'YYYY-MM-DD HH24:mi:ss'), 'DD') )");
			sql.append(") A WHERE (A.value_<'45' OR A.value_>'60')");
		System.out.println("sql="+sql.toString());
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	@Override
	public List<Map<String, Object>> getArData(String code, String column, String typeCode)
			throws Exception {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append(" select b.order_no ORDER_NO,b.code_name " + column.toUpperCase() + ", count(a." + column + ") SUM");
		sql.append(" from t_config_code b left join t_ar_" + code + "_base a ");
		sql.append(" on a." + column + " =b.code_name where b.parent_code=" + typeCode);
		sql.append(" group by b.order_no,b.code_name");
		sql.append(" order by b.order_no,b.code_name");
		return this.jdbcTemplate.queryForList(sql.toString());
	}

	@Override
	public List<Map<String, Object>> getArUseCharts(String time, String[] arCode, String[] arCodeName)
			throws Exception {
		// TODO Auto-generated method stub
		String col = "";
		String in = "";
		StringBuffer sql = new StringBuffer();
		if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
			for(int i=0; i<arCode.length; i++){
				in += (i==0?"'":",'") + arCode[i].toUpperCase() + "'";
				col += (i==0?"":",") + "nvl(sum((case b.archive_type_id when '" + arCode[i].toUpperCase() + "' then 1 else 0 end)),0) " + arCodeName[i].toUpperCase();
			}
			
			sql.append(" select " + col);
			sql.append(" from t_archive_recep_exborrow a, t_archive_recep_exborrow_e_r b");
			sql.append(" where trunc(to_date(a.exborrow_data, 'YYYY-MM-DD'),'MM') = trunc(to_date('" + time + "','YYYY-MM'))");
			sql.append(" and b.archive_type_id in(" + in + ")");
			sql.append(" and a.id=b.exborrow_id");
		}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
			for(int i=0; i<arCode.length; i++){
				in += (i==0?"'":",'") + arCode[i].toUpperCase() + "'";
				col += (i==0?"":",") + "isnull(sum((case b.archive_type_id when '" + arCode[i].toUpperCase() + "' then 1 else 0 end)),0) " + arCodeName[i].toUpperCase();
			}
			sql.append(" select " + col);
			sql.append(" from t_archive_recep_exborrow a, t_archive_recep_exborrow_e_r b");
			sql.append(" where SUBSTRING(a.exborrow_data, 1,7) = SUBSTRING('" + time + "',1,7)");
			sql.append(" and b.archive_type_id in(" + in + ")");
			sql.append(" and a.id=b.exborrow_id");
		}
		return this.jdbcTemplate.queryForList(sql.toString());
	}

	@Override
	public List<Map<String, Object>> getStoreInfo() throws Exception {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
			sql.append("select a.id, a.storage_name, nvl(sum(b.real_loadsum),0) sum");
			sql.append(" from t_storage_info a, t_storage_physical_locationg b");
			sql.append(" where a.storage_no=b.storage_id");
			sql.append(" having nvl(sum(b.real_loadsum),0) > 0");
			sql.append(" group by a.id, a.storage_name, a.storage_no");
		}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
			sql.append("select a.id, a.storage_name, isnull(sum(b.real_loadsum),0) sum");
			sql.append(" from t_storage_info a, t_storage_physical_locationg b");
			sql.append(" where a.storage_no=b.storage_id");
			sql.append(" group by a.id, a.storage_name, a.storage_no");
			sql.append(" having isnull(sum(b.real_loadsum),0) > 0");
		}
		sql.append(" order by a.id");
		return this.jdbcTemplate.queryForList(sql.toString());
	}

	@Override
	public List<Map<String, Object>> getStoreArInfo(String storageId, String[] arCode, String[] arCodeName) throws Exception {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		String col = "";
		String in = "";
		for(int i=0; i<arCode.length; i++){
			in += (i==0?"'":",'") + arCode[i].toUpperCase() + "'";
			col += (i==0?"":",") + "nvl(sum((case a.dalx when '" + arCode[i].toUpperCase() + "' then a.real_loadsum else 0 end)),0) " + arCodeName[i].toUpperCase();
		}
		sql.append(" select " + col);
		sql.append(" from t_storage_physical_locationg a where a.storage_id = " + storageId + " and a.dalx is not null and a.dalx in(" + in + ")");
		return this.jdbcTemplate.queryForList(sql.toString());
	}

	@Override
	public List<Map<String, Object>> getStoreArInfoByFons(String storageId)
			throws Exception {
		// TODO Auto-generated method stub
		List<Map<String, Object>> data = null;
		StringBuffer sql = new StringBuffer();
		
		//获取档案类型code
		sql.append("select distinct a.dalx from t_storage_physical_locationg a where a.dalx is not null order by  a.dalx");
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql.toString());
		
		if(list!=null&&list.size()>0){
			sql.setLength(0);
			StringBuffer inSql = new StringBuffer();
			for(int i=0; i<list.size(); i++){
				Map<String, Object> vals = (Map<String, Object>)list.get(i);
				String code = vals.get("DALX").toString().toUpperCase();
				String arTbName = "t_ar_" + code + "_base";
				inSql.append(i==0?"":" union all");
				inSql.append(" select c.qzh, count(1) cnt");
				inSql.append(" from t_storage_physical_locationg a, t_storage_diaodangg b, " + arTbName + " c");
				inSql.append(" where a.id = b.location_id and a.storage_id=b.storage_id and a.storage_id = '" + storageId + "'");
				inSql.append(" and c.unique_code = b.record_id and b.archive_type_id = '" + code + "'");
				inSql.append(" and a.dalx = b.archive_type_id ");
				inSql.append(" group by c.qzh");
			}
			
			sql.append("select a.id, t.qzh, a.fonds_name, sum(t.cnt) sum");
			sql.append(" from (" + inSql.toString() + ") t, T_ARCHIVE_DAILY_FONDS a");
			sql.append(" where a.fonds_code = t.qzh");
			sql.append(" group by a.id, t.qzh, a.fonds_name order by a.id");
			
			data = this.jdbcTemplate.queryForList(sql.toString());
		}
		
		return data;
	}

	@Override
	public List<Map<String, Object>> getLiyongData() throws Exception {
		// TODO Auto-generated method stub
				StringBuffer sql = new StringBuffer();
				if(CommonUtil.dbType==SqlUtil.DB_ORACLE){
					sql.append(" select substr(t.exborrow_data, 1, 7) month, ");
					sql.append(" LISTAGG(t.using_aim, ',') WITHIN GROUP(ORDER BY t.using_aim) AS using_aim ");
					sql.append(" from T_ARCHIVE_RECEP_EXBORROW t ");
					sql.append(" group by substr(t.exborrow_data, 1, 7) ");
					sql.append(" order by month asc ");
				}else if(CommonUtil.dbType==SqlUtil.DB_SQLSERVER){
					sql.append(" select MONTH,SUM from ");
					sql.append(" (select SUBSTRING(a.exborrow_data, 1, 7) MONTH, count(1) SUM");
					sql.append(" from t_archive_recep_exborrow a ");
					sql.append(" group by SUBSTRING(a.exborrow_data, 1, 7)) t");
					sql.append(" order by t.MONTH");
				}
				System.out.println("sql="+sql.toString());
				return jdbcTemplate.queryForList(sql.toString());
	}
	
	
	@Override
	public List<Map<String, Object>> getQuanzongData(String type) throws Exception {
		// TODO Auto-generated method stub
				StringBuffer sql = new StringBuffer();
				sql.append("select  distinct (qzh|| ',' ||CODE_ITEM ) as type ,qzmc from V_AR_ALLINFO_ZT t where t.CODE_ITEM like '%文书%' and qzh like '"+type+"%' order by (qzh|| ',' ||CODE_ITEM)");
				System.out.println("sql="+sql.toString());
				
				return jdbcTemplate.queryForList(sql.toString());
	}
	
	@Override
	public String getQuanzongNdData(String code_item, String qzh) throws Exception {
		// TODO Auto-generated method stub
				StringBuffer sql = new StringBuffer();
				sql.append("select count(*) from (select distinct nd  from V_AR_ALLINFO_ZT t where ");
				
				if("".equals(code_item) || " ".equals(code_item)) {
					sql.append("t.CODE_ITEM is null ");
				}else {
					sql.append("t.CODE_ITEM ='"+code_item+"' ");
				}
				
				if("".equals(qzh) || " ".equals(qzh)) {
					sql.append("and t.qzh is null ");
				}else {
					sql.append("and t.qzh ='"+qzh+"' ");
				}
				
				sql.append("group by nd order by nd)");
				//System.out.println("sql="+sql.toString());
				
				return (String) jdbcTemplate.queryForObject(sql.toString(),String.class);
	}
	
	
	@Override
	public List<String> getQuanzongNdHData(String code_item, String qzh) throws Exception {
		// TODO Auto-generated method stub
				StringBuffer sql = new StringBuffer();
				
				final List<String> list = new ArrayList<String>();
				list.clear();
				
				sql.append("select distinct nd  from V_AR_ALLINFO_ZT t where ");
				
				if("".equals(code_item) || " ".equals(code_item)) {
					sql.append("t.CODE_ITEM is null ");
				}else {
					sql.append("t.CODE_ITEM ='"+code_item+"' ");
				}
				
				if("".equals(qzh) || " ".equals(qzh)) {
					sql.append("and t.qzh is null ");
				}else {
					sql.append("and t.qzh ='"+qzh+"' ");
				}
				
				sql.append("group by nd order by nd");
				System.out.println("sql="+sql.toString());
				
					jdbcTemplate.query(sql.toString(), new RowMapper(){

					@Override
					public Object mapRow(ResultSet rs, int i) throws SQLException {
						list.add(rs.getString("nd"));
						return null;
					}
				});
				return  list;
	}
}
